﻿/******************************************************   
创建者: ljj
创建时间: 2018-08-31
修改:
修改时间: 
功能: 数据源
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.db.Models.Hkerp;
using com.nm.library.Helper;
using george.Web;
using NHibernateVertica;
using WeiXinAPI.model;
using System.Configuration;
using com.nm;
using WeiXinSdkDemo.Tools;

namespace WeiXinAPI
{
    [Description("数据源")]
    public class WxDataSourceHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();


        //[MustLogin]
        [Description("根据加盟商权限获取店铺")]
        [ReturnType(typeof(IList<DataSourceModel.ReturnModel>))]
        public ApiResult GetShopListByUser([ParamDescription("用户ID")] string UserID = "")
        {
            try
            {
                var sql = $@"SELECT  t.ShopID AS ID,
        t.ShopCode AS Code,
        t.ShopName AS Name
FROM    dws.dws_dim_shop t
WHERE   ( SELECT    COUNT(*)
          FROM      dws.dws_nm_franchisee_permission
          WHERE     QueryFlag = 1
                    AND UserID = '{UserID}'
        ) <> 0
        AND t.CompanyNo = 'NM'
        AND t.ShopState = '正常营业'
UNION
SELECT  d.ShopID AS ID,
        d.ShopCode AS Code,
        d.ShopName AS Name
FROM    dws.dws_nm_franchisee_permission  t1
        JOIN dws.dws_dim_shop d ON t1.ShopCode = d.ShopCode
                              
                               AND CompanyNo = 'NM'
                               AND ShopState = '正常营业'
WHERE   t1.QueryFlag = 2
        AND t1.UserID = '{UserID}'";
                var connection = ConfigurationManager.ConnectionStrings["Vertica21"].ConnectionString;
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<DataSourceModel.ReturnModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }

        //[MustLogin]
        [Description("在ERP中获取店铺")]
        [ReturnType(typeof(IList<DataSourceModel.ReturnModel>))]
        public ApiResult GetShopListByErp([ParamDescription("省份ID")] string ProvinceID = "", [ParamDescription("城市ID")] string CityID = "")
        {
            try
            {
                var sql = $@"SELECT  t.ShopID AS ID,
        t.ShopCode AS Code,
        t.shopname AS Name
FROM    HKERP_NOLIVE.dbo.bas_shop t
        LEFT JOIN HKERP_NOLIVE.dbo.Bas_Area a ON t.AreaID = a.AreaID
WHERE   shopid NOT IN ( 'NM000005' )
        {(string.IsNullOrEmpty(ProvinceID) ? "" : $"AND a.ParentID = '{ProvinceID}'")}
        {(string.IsNullOrEmpty(CityID) ? "" : $"AND t.AreaID = '{CityID}'")}
UNION ALL
SELECT  t.ShopID AS ID,
        t.ShopCode AS Code,
        t.shopname AS Name
FROM    HKERP_NOLIVE.dbo.GN_bas_shop t
WHERE   1=1
        {(string.IsNullOrEmpty(ProvinceID) ? "" : $"AND t.AreaID = '{ProvinceID}'")}
        {(string.IsNullOrEmpty(CityID) ? "" : $"AND t.AreaID = '{CityID}'")}";
                var dc = new HKERPEntities();
                var list = dc.Database.SqlQuery<DataSourceModel.ReturnModel>(sql);
                return Success(list.ToList());
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }


        //[MustLogin]
        [Description("在ERP中获取品类")]
        [ReturnType(typeof(IList<DataSourceModel.ReturnModel>))]
        public ApiResult GetKindListByErp()
        {
            try
            {
                var sql = @"select distinct kindid AS ID, kindname AS Name
from HKERP_NOLIVE.dbo.sd_mat_kind";
                var dc = new HKERPEntities();
                var list = dc.Database.SqlQuery<DataSourceModel.ReturnModel>(sql).ToList();
                return Success(list);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }

        //[MustLogin]
        [Description("获取自定义品类")]
        [ReturnType(typeof(IList<DataSourceModel.ReturnModel>))]
        public ApiResult GetKindListByJson()
        {
            try
            {
                var json =
                    myCache.GetFile<IList<DataSourceModel.ReturnModel>>("kindJson", "~/assets/json/kind.json");
                return Success(json);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }


        //[MustLogin]
        [Description("根据用户权限获取店铺")]
        [ReturnType(typeof(IList<DataSourceModel.ReturnModel>))]
        public ApiResult GetShopListByReport([ParamDescription("用户ID")] string UserID = "",
            [ParamDescription("省份ID")] string ProvinceID = "", [ParamDescription("城市ID")] string CityID = "")
        {
            try
            {
                var sql = $@"SELECT  t1.shopid AS ID,
        t1.shopname AS Name,
        t1.shopcode AS Code
FROM    dws.dws_nm_report_permission t
        JOIN ( SELECT   shopid ,
                        shopname ,
                        shopcode ,
                        a.AreaID ,
                        a.ParentCode AS ParentID
               FROM     dws.dws_dim_shop s
                        JOIN dwd.dwd_base_area a ON s.CompanyNo = a.ExtrSource
                                                    AND s.CityCode = a.AreaCode
             ) t1 ON t.ShopCode = t1.shopcode
WHERE   t.ShopCode <> 'all'
        AND t.ShopCode <> 'ALL'
        AND UserID = '{UserID}'
        {(string.IsNullOrEmpty(ProvinceID) ? "" : $"AND t1.ParentID = '{ProvinceID}'")}
        {(string.IsNullOrEmpty(CityID) ? "" : $"AND t1.AreaID = '{CityID}'")}
UNION
SELECT  t1.shopid AS ID,
        t1.shopname AS Name,
        t1.shopcode AS Code
FROM    dws.dws_nm_report_permission t
        JOIN ( SELECT   shopid ,
                        shopname ,
                        shopcode ,
                        a.AreaID ,
                        a.ParentCode AS ParentID
               FROM     dws.dws_dim_shop s
                        JOIN dwd.dwd_base_area a ON s.CompanyNo = a.ExtrSource
                                                    AND s.CityCode = a.AreaCode
             ) t1 ON 1 = 1
WHERE   ( t.ShopCode = 'all'
          OR t.ShopCode = 'ALL'
        )
        AND UserID = '{UserID}'
        {(string.IsNullOrEmpty(ProvinceID) ? "" : $"AND t1.ParentID = '{ProvinceID}'")}
        {(string.IsNullOrEmpty(CityID) ? "" : $"AND t1.AreaID = '{CityID}'")}";
                var connection = ConfigurationManager.ConnectionStrings["Vertica21"].ConnectionString;
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<DataSourceModel.ReturnModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }

        //[MustLogin]
        [Description("获取省份")]
        [ReturnType(typeof(IList<DataSourceModel.ReturnModel>))]
        public ApiResult GetProvince()
        {
            try
            {
                var sql = $@"SELECT DISTINCT
        [AreaID] AS ID,
        [AreaName] AS Name
FROM    [HKERP_NOLIVE].[dbo].[Bas_Area]
WHERE   Level = 1
        AND AllowUsed = 1";
                var dc = new HKERPEntities();
                var list = dc.Database.SqlQuery<DataSourceModel.ReturnModel>(sql).ToList();
                return Success(list);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }

        //[MustLogin]
        [Description("获取城市")]
        [ReturnType(typeof(IList<DataSourceModel.ReturnModel>))]
        public ApiResult GetCity([ParamDescription("省份ID")] string ID = "")
        {
            try
            {
                var sql = $@"SELECT DISTINCT
        [AreaID] AS ID,
        [AreaName] AS Name
FROM    [HKERP_NOLIVE].[dbo].[Bas_Area]
WHERE   Level = 2
        AND AllowUsed = 1
        {(string.IsNullOrEmpty(ID) ? "" : $" AND ParentID = '{ID}' ")}";
                var dc = new HKERPEntities();
                var list = dc.Database.SqlQuery<DataSourceModel.ReturnModel>(sql).ToList();
                return Success(list);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }

        //[MustLogin]
        [Description("获取性别")]
        [ReturnType(typeof(IList<DataSourceModel.ReturnModel>))]
        public ApiResult GetSex()
        {
            try
            {
                var sql = $@"SELECT SexID AS ID,SexName AS Name FROM HKERP_NOLIVE.dbo.SD_Mat_Sex WHERE AllowUsed=1";
                var dc = new HKERPEntities();
                var list = dc.Database.SqlQuery<DataSourceModel.ReturnModel>(sql).ToList();
                return Success(list);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }

        //[MustLogin]
        [Description("获取季节")]
        [ReturnType(typeof(IList<DataSourceModel.ReturnModel>))]
        public ApiResult GetSeason([ParamDescription("时间")]DateTime? dateTime = null)
        {
            try
            {
                var sql = $@"SELECT  SeasonID AS ID,
        SeasonName AS Name
FROM    HKERP_NOLIVE.dbo.SD_Mat_Season
WHERE   AllowUsed = 1
        {(dateTime.HasValue ? $"AND StartMonth <= {dateTime.Value.Month}" : "")}
        {(dateTime.HasValue ? $"AND EndMonth >= {dateTime.Value.Month}" : "")}";
                var dc = new HKERPEntities();
                var list = dc.Database.SqlQuery<DataSourceModel.ReturnModel>(sql).ToList();
                return Success(list);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }

        //[MustLogin]
        [Description("获取报表类型")]
        [ReturnType(typeof(IList<DataSourceModel.ReturnModel>))]
        public ApiResult GetReportType()
        {
            try
            {
                var json =
                    myCache.GetFile<IList<DataSourceModel.ReturnModel>>("reportTypeJson",
                        "~/assets/json/reportType.json");
                return Success(json);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }

        [MustLogin]
        [Description("获取Api")]
        [ReturnType(typeof(IList<DataSourceModel.ApiModel>))]
        public ApiResult GetApiName([ParamDescription("文本描述")] string Text = "")
        {
            try
            {
                var query = System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.GetMethods()
                    .Where(d => d.ReturnType.FullName == "com.nm.ApiServer.ApiResult" && d.Name != "GetApiName")
                    .Select(d => d).ToList();
                var list = new List<DataSourceModel.ApiModel>();
                foreach (var item in query)
                {
                    foreach (var ca in item.CustomAttributes)
                    {
                        if (ca.AttributeType.Name == "DescriptionAttribute")
                        {
                            if (ca.ConstructorArguments.Any(d => d.Value.ToString().Contains(Text)))
                            {
                                list.Add(new DataSourceModel.ApiModel
                                {
                                    Api = item.Name,
                                    ApiName = (ca.ConstructorArguments.FirstOrDefault().Value ?? "").ToString()
                                });
                            }
                        }
                    }
                }
                return Success(list.DistinctBy(d => d.Api).ToList());
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }
    }
}
